﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using RCREEE.Library.Entities;

namespace RCREEE.Library
{
    public static partial class DataManager
    {
        

        #region Items

        public static ItemPageMasterViewModel GetItemCountryMasterValues(int countryId, int itemId, int languageCode)
        {
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "GetCountryMultiValueItemHeaderForEntity";

            _Command.Parameters.Clear();

            if (countryId > 0)
            {
                _Command.Parameters.Add(new SqlParameter
                                                         {
                                                             ParameterName = "@CountryId",
                                                             SqlDbType = SqlDbType.Int,
                                                             Value = countryId,
                                                         });
            }

            _Command.Parameters.Add(new SqlParameter
           {
               ParameterName = "@itemId",
               SqlDbType = SqlDbType.Int,
               Value = itemId,
           });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@LanguageCode",
                SqlDbType = SqlDbType.Int,
                Value = languageCode,
            });


            _DataSet = new DataSet();
            _Adapter.SelectCommand = _Command;
            _Adapter.Fill(_DataSet);

            ItemPageMasterViewModel model = new ItemPageMasterViewModel();

            DataTable itemsTable = _DataSet.Tables[0];
            if (itemsTable != null)
            {
                if (itemsTable.Rows.Count > 0)
                {
                    model.Item = new TinyItem { ID = itemId };
                    if (itemsTable.Rows[0]["itemName"] != null)
                    {
                        model.Item.Name = itemsTable.Rows[0]["itemName"].ToString();
                    }
                }
            }


            DataTable multiValueHeadersTable = _DataSet.Tables[1];
            if (multiValueHeadersTable != null)
            {
                model.MultiValueItems = new List<MultiValueItem>();
                if (multiValueHeadersTable.Rows.Count > 0)
                {
                    for (int i = 0; i < multiValueHeadersTable.Rows.Count; i++)
                    {

                        MultiValueItem multiValueItem = new MultiValueItem();
                        if (multiValueHeadersTable.Rows[i]["MultiValueItemId"] != null)
                        {
                            int multiValueItemId;
                            if (int.TryParse(multiValueHeadersTable.Rows[i]["MultiValueItemId"].ToString(), out multiValueItemId) &&
                                multiValueItemId > 0)
                            {
                                multiValueItem.MultiValueItemId = multiValueItemId;
                            }
                        }

                        if (multiValueHeadersTable.Rows[i]["CountryId"] != null && multiValueHeadersTable.Rows[i]["CountryName"] != null)
                        {
                            multiValueItem.Country = new Country();
                            int itemCountryId;
                            if (int.TryParse(multiValueHeadersTable.Rows[i]["CountryId"].ToString(), out itemCountryId) &&
                                itemCountryId > 0)
                            {
                                multiValueItem.Country.CountryId = itemCountryId;
                            }
                            if (multiValueHeadersTable.Rows[i]["CountryName"] != null)
                            {
                                multiValueItem.Country.CountryName = multiValueHeadersTable.Rows[i]["CountryName"].ToString();
                            }
                             if (multiValueHeadersTable.Rows[i]["FlagImagePath"] != null)
                                 multiValueItem.Country.FlagImagePath = multiValueHeadersTable.Rows[i]["FlagImagePath"].ToString();
                        }
                        model.MultiValueItems.Add(multiValueItem);
                    }
                }
            }

            FillCountryMultiItemData(model, languageCode);

            return model;


        }

        private static void FillCountryMultiItemData(ItemPageMasterViewModel model, int languageCode)
        {
            foreach (MultiValueItem multiValueItem in model.MultiValueItems)
            {
                #region SQL parameters and commands
                _Command.CommandType = CommandType.StoredProcedure;
                _Command.CommandText = "GetCountryMultiValueItemValuesAndCrades";

                _Command.Parameters.Clear();
                _Command.Parameters.Add(new SqlParameter
                {
                    ParameterName = "@MultiValueItemId",
                    SqlDbType = SqlDbType.Int,
                    Value = multiValueItem.MultiValueItemId,
                });
                _Command.Parameters.Add(new SqlParameter
                {
                    ParameterName = "@LanguageCode",
                    SqlDbType = SqlDbType.Int,
                    Value = languageCode,
                });
                #endregion


                _DataSet = new DataSet();
                _Adapter.SelectCommand = _Command;
                _Adapter.Fill(_DataSet);



                #region Add Values
                DataTable valuesTable = _DataSet.Tables[0];
                if (valuesTable != null)
                {
                    multiValueItem.MultiValueItemDataValues = new List<MultiValueItemDataValue>();
                    if (valuesTable.Rows.Count > 0)
                    {
                        for (int i = 0; i < valuesTable.Rows.Count; i++)
                        {

                            MultiValueItemDataValue value = new MultiValueItemDataValue();
                            if (valuesTable.Rows[i]["MultiValueItemValueId"] != null && valuesTable.Rows[i]["Value"] != null)
                            {
                                int valueId;
                                if (int.TryParse(valuesTable.Rows[i]["MultiValueItemValueId"].ToString(), out valueId) &&
                                    valueId > 0)
                                {
                                    value.MultiValueItemDataValueId = valueId;
                                }

                                if (valuesTable.Rows[i]["Value"] != null)
                                {
                                    value.Value = valuesTable.Rows[i]["Value"].ToString();
                                }
                            }
                            multiValueItem.MultiValueItemDataValues.Add(value);
                        }
                    }
                }
                #endregion


                #region Add Cadres
                DataTable cradesTable = _DataSet.Tables[1];
                if (cradesTable != null)
                {
                    multiValueItem.Cadres = new List<Cadre>();
                    if (cradesTable.Rows.Count > 0)
                    {
                        for (int i = 0; i < cradesTable.Rows.Count; i++)
                        {

                            Cadre cadre = new Cadre();
                            if (cradesTable.Rows[i]["CadreId"] != null && cradesTable.Rows[i]["Cadre"] != null)
                            {
                                int cadreId;
                                if (int.TryParse(cradesTable.Rows[i]["CadreId"].ToString(), out cadreId) &&
                                    cadreId > 0)
                                {
                                    cadre.CadreId = cadreId;
                                }

                                if (cradesTable.Rows[i]["Cadre"] != null)
                                {
                                    cadre.CadreValue = cradesTable.Rows[i]["Cadre"].ToString();
                                }
                            }
                            multiValueItem.Cadres.Add(cadre);
                        }
                    }
                }
                #endregion


            }


        }

        public static void InsertMultiItemValueCadre(int multiValueItemId, int languageCode, string cadre)
        {
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "InsertCountryMultiValueItemCrades";

            _Command.Parameters.Clear();


            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@MultiValueItemId",
                SqlDbType = SqlDbType.Int,
                Value = multiValueItemId,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@LanguageCode",
                SqlDbType = SqlDbType.Int,
                Value = languageCode,
            });

            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@Cadre",
                SqlDbType = SqlDbType.NVarChar,
                Value = cadre,
            });


            _Adapter.InsertCommand = _Command;

            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                _Command.ExecuteNonQuery();

                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }
        }

        public static void EditMultiItemValuCadre(int cadreId, string cadre)
        {
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "EditCountryMultiValueItemCadre";

            _Command.Parameters.Clear();


            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@CadreId",
                SqlDbType = SqlDbType.Int,
                Value = cadreId,
            });

            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@Cadre",
                SqlDbType = SqlDbType.NVarChar,
                Value = cadre,
            });


            _Adapter.InsertCommand = _Command;

            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                _Command.ExecuteNonQuery();

                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }
        }
        public static void DeleteMultiItemValuCadre(int cadreId)
        {
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "DeleteMultiValueItemCadre";

            _Command.Parameters.Clear();

            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@CadreId",
                SqlDbType = SqlDbType.Int,
                Value = cadreId,
            });

            _Adapter.DeleteCommand = _Command;

            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                _Command.ExecuteNonQuery();

                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }
        }

        public static void InsertMultiItemValueValue(int multiValueItemId, int languageCode, string value)
        {
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "InsertCountryMultiValueItemValue";

            _Command.Parameters.Clear();


            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@MultiValueItemId",
                SqlDbType = SqlDbType.Int,
                Value = multiValueItemId,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@LanguageCode",
                SqlDbType = SqlDbType.Int,
                Value = languageCode,
            });

            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@Value",
                SqlDbType = SqlDbType.NVarChar,
                Value = value,
            });


            _Adapter.InsertCommand = _Command;

            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                _Command.ExecuteNonQuery();

                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }
        }

        public static void EditMultiItemValuValue(int valueId, string value)
        {
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "EditCountryMultiValueItemValue";

            _Command.Parameters.Clear();


            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@ValueId",
                SqlDbType = SqlDbType.Int,
                Value = valueId,
            });

            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@Value",
                SqlDbType = SqlDbType.NVarChar,
                Value = value,
            });


            _Adapter.InsertCommand = _Command;

            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                _Command.ExecuteNonQuery();

                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }
        }

        public static void DeleteMultiItemValuValue(int valueId)
        {
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "DeleteMultiValueItemValue";

            _Command.Parameters.Clear();


            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@ValueId",
                SqlDbType = SqlDbType.Int,
                Value = valueId,
            });

            _Adapter.DeleteCommand = _Command;

            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                _Command.ExecuteNonQuery();

                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }
        }
        public static int InsertMultiValueItem(int countryId, int entityId)
        {
            int multiValueItemId = 0;
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "InsertCountryMultiValueItem";

            _Command.Parameters.Clear();


            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@CountryId",
                SqlDbType = SqlDbType.Int,
                Value = countryId,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@EntityId",
                SqlDbType = SqlDbType.Int,
                Value = entityId,
            });


            _Adapter.InsertCommand = _Command;

            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                var returnedValue = _Command.ExecuteScalar();

                if (returnedValue != null)
                {
                    int.TryParse(returnedValue.ToString(), out multiValueItemId);
                }

                // Close Connection)
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }
            return multiValueItemId;
        }
        #endregion

        public static List<string> GetMultiValueItemValues(int itemId, int languageCode)
        {
            _Command.CommandText = "GetMultiValueItemValues";
            _Command.CommandType = CommandType.StoredProcedure;

            _Command.Parameters.Clear();

            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@MultiValueItemId",
                SqlDbType = SqlDbType.Int,
                Value = itemId,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@LanguageCode",
                SqlDbType = SqlDbType.Int,
                Value = languageCode,
            });

            _DataSet = new DataSet();


            _Adapter.SelectCommand = _Command;
            try
            {
                _Adapter.Fill(_DataSet);
            }
            catch (Exception exp)
            {

            }
            if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
            {
                List<string> _lstValues = new List<string>();
                foreach (DataRow dr in _DataSet.Tables[0].Rows)
                {
                    _lstValues.Add(Convert.ToString(dr["Value"]));
                }
                return _lstValues;
            }
            return null;
        }
    }
}
